package com.ray.services.base;

import com.alibaba.fastjson.JSONObject;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;
import com.jfinal.plugin.activerecord.SqlPara;
import com.ray.model.DataField;
import com.ray.util.Commen;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.cache.Cache;
import org.springframework.cache.CacheManager;
import org.springframework.cache.annotation.Cacheable;
import org.springframework.stereotype.Service;

import java.util.*;
import java.util.Date;

/**
 * 提供自动缓存的数据库操作服务
 */
@Service
public class DbService {

    @Value("${spring.datasource.base.name}")
    private String base;

    @Value("${rayboot.domin_path}")
    public String domin_path;

    @Value("${rayboot.delete_mode}")
    private String delete_mode;

    @Autowired
    private CacheManager cacheManager;

    /**
     * 获取第一条数据
     * @param data_source 数据库
     * @param sql 查询语句
     * @return
     */
    public Record findFirst(String data_source, String sql){
        return Db.use(data_source).findFirst(sql);
    }

    /**
     * 获取数据
     * @param data_source 数据库
     * @param table_name 表名（缓存必须传值）
     * @param sql 查询语句
     * @param cacheStatu 是否使用缓存
     * @return
     */
    public List<Record> find(String data_source, String table_name, String sql, Boolean cacheStatu){
        List<Record> value = null;
        if(cacheStatu){
            Cache cache = cacheManager.getCache(data_source+"-"+table_name);
            // 根据key从缓存中获取数据，如果没有则执行方法体并缓存结果
            value = cache.get(sql,List.class);
            if (value == null) {
                System.out.println("从数据库中获取数据raytabelList");
                value = Db.use(data_source).find(sql);
                cache.put(sql, value);
            }
        }else{
            value = Db.use(data_source).find(sql);
        }
        return value;
    }

    /**
     * 获取分页数据
     * @param data_source 数据库
     * @param table_name 表名（缓存必须传值）
     * @param sql 查询语句
     * @param currentPage 当前页
     * @param pageSize 每页显示条数
     * @return
     */
    public Page<Record> paginate(String data_source, String table_name, SqlPara sql, int currentPage, int pageSize,Boolean cacheStatu){
        Page<Record> value = null;
        if(cacheStatu){
            Cache cache = cacheManager.getCache(data_source+"-"+table_name);
            String key = sql.getSql()+"-"+currentPage+"-"+pageSize;
            // 根据key从缓存中获取数据，如果没有则执行方法体并缓存结果
            value = cache.get(key,Page.class);
            if (value == null) {
                System.out.println("从数据库中获取数据raytabelPageList");
                value = Db.use(data_source).paginate(currentPage,pageSize,sql);
                cache.put(key, value);
            }
        }else{
            value = Db.use(data_source).paginate(currentPage,pageSize,sql);
        }
        return value;
    }

    /**
     * 新增数据
     * @param data_source 数据库
     * @param table_name 表名
     * @param model 新增的数据
     */
    public void save(String data_source,String table_name,Record model){
        Cache cache = cacheManager.getCache(data_source+"-"+table_name);
        if (cache != null) {
            cache.clear();
        }
        Db.use(data_source).save(table_name, model);
    }

    /**
     * 批量新增数据（缓存）
     * @param data_source 数据库
     * @param table_name 表名
     * @param list 新增的数据
     */
    public void batchSave(String data_source,String table_name,List<Record> list){
        Cache cache = cacheManager.getCache(data_source+"-"+table_name);
        if (cache != null) {
            cache.clear();
        }
        Db.use(data_source).batchSave(table_name, list,list.size());
    }

    /**
     * 修改数据
     * @param data_source 数据库
     * @param table_name 表名
     * @param model 新增的数据
     */
    public void update(String data_source,String table_name,Record model){
        Cache cache = cacheManager.getCache(data_source+"-"+table_name);
        if (cache != null) {
            cache.clear();
        }
        Db.use(data_source).update(table_name, model);
    }

    /**
     * 修改数据
     * @param data_source 数据库
     * @param table_name 表名
     * @param sql 修改语句
     */
    public void update(String data_source,String table_name,String sql){
        Cache cache = cacheManager.getCache(data_source+"-"+table_name);
        if (cache != null) {
            cache.clear();
        }
        Db.use(data_source).update(sql);
    }

    /**
     * 删除数据
     * @param data_source 数据库
     * @param table_name 表名
     * @param model 新增的数据
     * @param userid 用户id
     */
    public void delete(String data_source,String table_name,Record model,String userid){
        Cache cache = cacheManager.getCache(data_source+"-"+table_name);
        if (cache != null) {
            cache.clear();
        }
        if ("delete".equals(delete_mode)) {
            Db.use(data_source).deleteById(table_name, model.getInt("id"));
        } else {
            model.set("is_delete", 1);
            model.set("del_time", new Date());
            model.set("del_user_id", userid);
            Db.use(data_source).update(table_name, model);
        }
    }

    /**
     * @note 根据元对象下的字段进行sql格式化
     * @param tableName 表名
     * @param fields 元数据下的字段列表
     * @param queryForm 查询表单
     * @time 2022年9月28日 下午3:06:51
     * @author Eddie_Ray
     */
    public String sqlFormat(String tableName, List<DataField> fields, String queryForm, String region, String base) {
        String sqlString = "select ";
        for (int i = 0; i < fields.size(); i++) {
            if (i > 0) {
                sqlString += ",";
            }
            if (fields.get(i).getIsFictitious()) {
                sqlString += "(" + fields.get(i).getFictitiousSql() + ") as " + fields.get(i).getEn();
            }else if("selector".equals(fields.get(i).getType())) {
                JSONObject config = JSONObject.parseObject(fields.get(i).getTypeConfig());
                if ("sql".equals(config.getString("type"))) {
                    sqlString = sqlString + fields.get(i).getEn() + " as " + fields.get(i).getEn();
                    sqlString = sqlString + "," + "(SELECT GROUP_CONCAT(" + config.getString("value") + ") FROM (" + config.getString("sql") +" "+ (config.getString("sql").contains("where") ?"":"where 1=1");
                    sqlString += ") temp WHERE FIND_IN_SET(" + config.getString("key") + ", t." + fields.get(i).getEn() + ")) " + fields.get(i).getEn() +"_show_value";
                } else {
                    sqlString = sqlString + fields.get(i).getEn();
                }
            }else if("user".equals(fields.get(i).getType())) {
                sqlString = sqlString + fields.get(i).getEn() + " as " + fields.get(i).getEn();
                sqlString = sqlString + "," + "(SELECT GROUP_CONCAT(username) FROM " + base + ".user WHERE is_delete = 0 and region = '"+region+"' and FIND_IN_SET(userid, t." + fields.get(i).getEn() + ")) " + fields.get(i).getEn() + "_show_value";
            }else {
                sqlString += fields.get(i).getEn();
            }
        }
        sqlString += " from " + tableName + " t where 1=1 ";
        JSONObject query = JSONObject.parseObject(queryForm);
        if (query != null) {
            for (int i = 0; i < fields.size(); i++) {
                if (/* fields.get(i).getIsQuery() && */!Commen.isEmptyy(query.get(fields.get(i).getEn()))) {
                    if (fields.get(i).getIsFictitious()) {
                        sqlString += " and (" + fields.get(i).getFictitiousSql() + ") like '"
                                + query.get(fields.get(i).getEn()).toString().trim() + "'";
                    } else if ("input".equals(fields.get(i).getType())) {
                        sqlString += " and " + fields.get(i).getEn() + " like '" + query.get(fields.get(i).getEn()).toString().trim()+ "'";
                    } else if ("selector".equals(fields.get(i).getType())) {
                        String[] array = query.get(fields.get(i).getEn()).toString().split(",");
                        sqlString += " and (";
                        for (int j = 0; j < array.length; j++) {
                            sqlString += fields.get(i).getEn() + " = '" + array[j] + "' || ";
                        }
                        sqlString = sqlString.substring(0, sqlString.length()-4);
                        sqlString += ")";
                    } else if ("select".equals(fields.get(i).getType()) || "radio".equals(fields.get(i).getType())) {
                        sqlString += " and " + fields.get(i).getEn() + " = '" + query.get(fields.get(i).getEn())+"'";
                    } else if ("date".equals(fields.get(i).getType()) || "datetime".equals(fields.get(i).getType())) {
                        String dateRange = query.get(fields.get(i).getEn()).toString();
                        String start = dateRange.split(",")[0].split("\"")[1];
                        String end = dateRange.split(",")[1].split("\"")[1];
                        sqlString += " and " + fields.get(i).getEn() + " >= '" + start + "'";
                        sqlString += " and " + fields.get(i).getEn() + " <= '" + end + "'";
                    }
                }
            }
        }
        return sqlString;
    }

    /*
     * @note 将修改后的数据行查询sql格式化
     * @time 2022年1月6日下午12:01:16
     * @author Ray
     */
    public String sqlFormatUpdate(String tableName,List<DataField> fields,Object id,String region,String userid,String base,String main) {
        String sqlString = "select ";
        for (int i = 0; i < fields.size(); i++) {
            if (i > 0) {
                sqlString += ",";
            }
            if (fields.get(i).getIsFictitious()) {
                sqlString += "(" + fields.get(i).getFictitiousSql() + ") as " + fields.get(i).getEn();
            }else if("selector".equals(fields.get(i).getType())) {
                JSONObject config = JSONObject.parseObject(fields.get(i).getTypeConfig());
                if ("sql".equals(config.getString("type"))) {
                    sqlString = sqlString + fields.get(i).getEn() + " as " + fields.get(i).getEn();
                    sqlString = sqlString + "," + "(SELECT GROUP_CONCAT(" + config.getString("value") + ") FROM (" + config.getString("sql") +" "+ (config.getString("sql").contains("where") ?"":"where 1=1");
                    sqlString += ") temp WHERE FIND_IN_SET(" + config.getString("key") + ", t." + fields.get(i).getEn() + ")) " + fields.get(i).getEn() +"_show_value";
                } else {
                    sqlString = sqlString + fields.get(i).getEn();
                }
            }else if("user".equals(fields.get(i).getType())) {
                sqlString = sqlString + fields.get(i).getEn() + " as " + fields.get(i).getEn();
                sqlString = sqlString + "," + "(SELECT GROUP_CONCAT(username) FROM " + base + ".user WHERE FIND_IN_SET(userid, t." + fields.get(i).getEn() + ")) " + fields.get(i).getEn() + "_show_value";
            }else {
                sqlString += fields.get(i).getEn();
            }
        }
        sqlString += " from " + tableName + " t where id = "+id;
        //region,userid模板
        sqlString = sqlString.replaceAll("#region", region)
                .replaceAll("#userid", userid)
                .replaceAll("#datasource_base",base)
                .replaceAll("#datasource_main",main);
        return sqlString;
    }

    /**
     * 获取下拉框和单选框和复选框的值（缓存）
     * @param sql
     * @return
     */
    @Cacheable(value = "selectRadioCheckbox",key = "#sql")
    public List<Record> getSelectRadioCheckboxList(String sql){
        System.out.println("从数据库中获取数据selectRadioCheckbox");
        return Db.use(base).find(sql);
    }

}
